package Server.BusinessObjects;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.eclipse.jdt.internal.compiler.ast.ThisReference;

import Server.DataAccess.ConnectJDBCMySQL;

public class Highscore {

	private int userId;
	private int scenId;
	private int catId;
	private int points;

	public Highscore() {

	}

	public int getUserId() {
		return userId;
	}

	public void setUserId(int userId) {
		this.userId = userId;
	}

	public int getCatId() {
		return catId;
	}

	public void setCatId(int catId) {
		this.catId = catId;
	}

	public int getPoints() {
		return points;
	}

	public void setPoints(int points) {
		this.points = points;
	}

	public int getScenId() {
		return scenId;
	}

	public void setScenId(int scenId) {
		this.scenId = scenId;
	}

	public Highscore(ResultSet rs) throws SQLException {
		this.setUserId(rs.getInt("userId"));
		this.setScenId(rs.getInt("scenId"));
		this.setCatId(rs.getInt("catId"));
		this.setPoints(rs.getInt("points"));
	}

	private static List<Highscore> getList(PreparedStatement ps)
			throws SQLException {
		List<Highscore> output = new ArrayList<Highscore>();

		try {
			ResultSet rs = ps.executeQuery();

			while (rs.next())
				output.add(new Highscore(rs));

			rs.close();
		} finally {
			Connection con = ps.getConnection();
			if (ps != null)
				ps.close();

			if (con != null)
				con.close();

		}

		return output;
	}
	
	private static List<Integer> getPoints(PreparedStatement ps)
			throws SQLException {
		List<Integer> output = new ArrayList<Integer>();

		try {
			ResultSet rs = ps.executeQuery();

			while (rs.next())
				output.add(rs.getInt("points"));

			rs.close();
		} finally {
			Connection con = ps.getConnection();
			if (ps != null)
				ps.close();

			if (con != null)
				con.close();

		}

		return output;
	}


	public static void insertHighscore(int userId, int scenId, int catId, int points)
			throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();
		
		List<Highscore> list = getHighscoreForUserAndCat(userId, scenId, catId);
		
		if(list.isEmpty()) {
			String insert = "INSERT INTO highscores (userId, scenId, catId, points) VALUES (?,?,?,?)";
			PreparedStatement preparedStatement = dbCon.prepareStatement(insert);
			preparedStatement.setInt(1, userId);
			preparedStatement.setInt(2, scenId);
			preparedStatement.setInt(3, catId);
			preparedStatement.setInt(4, points);
			preparedStatement.execute();
			// autocommit is on by default
			dbCon.close();
		} else {
			String update = "UPDATE `highscores` SET `points`=? WHERE `userId`=? AND `scenId`=? AND`catId`=?";
			PreparedStatement preparedStatement = dbCon.prepareStatement(update);
			preparedStatement.setInt(1, list.get(0).getPoints() + points);
			//preparedStatement.setInt(1, points);
			preparedStatement.setInt(2, userId);
			preparedStatement.setInt(3, scenId);
			preparedStatement.setInt(4, catId);
			preparedStatement.execute();
			// autocommit is on by default
			dbCon.close();
		}
	}

	public static List<Highscore> getHighscoreForUserAndCat(int userId,
			int scenId, int catId) throws SQLException {
		String query = "Select * from highscores WHERE userId = ? AND scenId = ? AND catId = ?";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, userId);
		ps.setInt(2, scenId);
		ps.setInt(3, catId);

		return Highscore.getList(ps);
	}

	public static List<Highscore> getHighscoreForUserAndScen(int userId,
			int scenId) throws SQLException {
		String query = "Select * from highscores WHERE userId = ? AND scenId = ?";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, userId);
		ps.setInt(2, scenId);

		return Highscore.getList(ps);
	}


	public static int getBestHighscoreInCategory(int scenId, int catId) throws SQLException {
		int result = 0;
		String query = "SELECT * FROM highscores WHERE scenId = ? AND catId =? ORDER BY points DESC LIMIT 1 ";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, scenId);
		ps.setInt(2, catId);
		
		List<Integer> points = getPoints(ps);
		
		for(Integer i : points){
			result=i;
			
		}
		
		return result;

	}

	public static int getStandingToUserInCategory(int userId, int scenId,
			int catId) throws SQLException {
		String query = "SELECT * FROM highscores WHERE scenId = ? AND catId =? ";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, scenId);
		ps.setInt(2, catId);
		
		List<Highscore> allHighscores=getList(ps);
		List<Highscore> myHighscore = Highscore.getHighscoreForUserAndCat(userId,
				scenId, catId);

		int myPoints=0;
		for (Highscore h1 : myHighscore) {
			myPoints += h1.getPoints();
		}
		int count=1;
		for(Highscore h : allHighscores){
			if(h.getPoints()>myPoints){
				count++;
			}
		}
		
		return count;
	}

	public static int getBestHighscoreInScenario(int scenId) throws SQLException {
		String query = "SELECT userId, SUM(points) AS points FROM highscores WHERE scenId =? GROUP BY userId ";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, scenId);
		
		List<Integer> points = getPoints(ps);
		
		int tmp=0;
		
		for(Integer i : points){
			if(i>tmp){
				tmp=i;
			}
		}
		
		return tmp;
	}

	public static int getStandingToUserInScenario(int userId, int scenId) throws SQLException {
		String query = "SELECT userId, SUM(points) AS points FROM highscores WHERE scenId =? GROUP BY userId ";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, scenId);
		
		List<Integer> points = getPoints(ps);
		int myPoints =0;
		List<Highscore> myHighscore = getHighscoreForUserAndScen(userId,scenId);
		for (Highscore h1 : myHighscore) {
			myPoints += h1.getPoints();
		}

		int count=1;
		for(Integer i : points){
			if(i>myPoints){
				count++;
			}
		}
		
		return count;
		
	}
}